package 中象

import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.primefaces.component.api.UIColumn
import org.primefaces.model.DefaultStreamedContent
import org.primefaces.util.SerializableSupplier

import javax.el.ValueExpression
import javax.faces.component.UIComponent
import java.time.Year


import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.component.datatable.DataTable;
import org.primefaces.component.column.Column;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;


public execute(o) {
    return new mc(o)
}

class mc {
    def mypara
    def whereList=["所属公司":[],"费用项目":[],"个人":[],"年份":[]]
    def _selectMap=["所属公司":null,"费用项目":null,"年份":null,"个人":null,"年份":null]
    public mc(m) {
        this.mypara = m;
        getwhereList()
        selsql()
    }
    List<Map> tableList=[]
    def rowsMap=[:]
    def rowsfun(){
        //key 是公司   value是最小的下标
        def cunzaiMap=[:]
        for(int i=0;i<tableList.size();i++) {
            def tableMap = tableList.get(i);
            if (!cunzaiMap.containsKey(tableMap.get("公司"))) {
                cunzaiMap.put(tableMap.get("公司"), i)
            }
            def key=tableMap.get("公司")+"_"+cunzaiMap.get(tableMap.get("公司"))
            if (rowsMap.containsKey(key)) {
                rowsMap.put(key, rowsMap.get(key) + 1)
            } else {
                rowsMap.put(key, 1)
            }
        }

    }
    def findCompanyCount(def value,def index){
        def key=value+"_"+index
        if(rowsMap.containsKey(key)){
            return rowsMap.get(key)
        }else{
            return 0
        }
    }
    def getwhereList(){
        def conn
        try{
            conn=groovy.sql.Sql.newInstance(mypara.hqcon(1))
            // 将年份转换为字符串
            Year currentYear = Year.now()
            def yearStr =  currentYear.toString()
            _selectMap["年份"]=yearStr
            for (int i = -10; i <= 10; i++) {
                whereList["年份"].add(currentYear.plusYears(i).toString())
            }

            whereList["个人"]=conn.rows("select * from wsd_user")
            whereList["所属公司"]=conn.rows("select * from bd_company where dr=1")
            whereList["费用项目"]=conn.rows("SELECT code as nid,name,code ,treecode  FROM  wsd_treetable  where  treecode='BXCostClass' and LEN(code) - LEN(REPLACE(code, '.', ''))=2")
        }catch(Exception  e){
            e.printStackTrace()
        }finally{
            if(conn!=null){
                conn.close()
            }
        }
    }
    def getName(type,code){
        def name=""
        whereList[type].each{
            if(it.code==code){
                name=it.name
            }
        }
        return name
    }
    def selsql(){
        def conn
        try{
            conn=groovy.sql.Sql.newInstance(mypara.hqcon(1))
            def where=" 1=1 "
            if(_selectMap.get("年份")){
                where+=" and (year(ywdate)="+_selectMap.get("年份")+" or ywdate is null)"
            }
            if(_selectMap.get("所属公司")){
                where+=" and orgcode in ('"+_selectMap.get("所属公司").join("','")+"')"
            }
            if(_selectMap.get("费用项目")){
                where+=" and nReimburserItem in ('"+_selectMap.get("费用项目").join("','")+"' )"
            }
            if(_selectMap.get("个人")){
                where+=" and nReimburserCode in ('"+_selectMap.get("个人").join("','")+"' )"
            }
            def sql="""SELECT 
    orgcode AS 公司,
    nReimburserItem AS 项目,
    nReimburserCode as 个人,
    SUM(CASE WHEN MONTH(ywdate) = 1 THEN decInvoiceAmoun ELSE 0 END) AS 一月金额,
    SUM(CASE WHEN MONTH(ywdate) = 2 THEN decInvoiceAmoun ELSE 0 END) AS 二月金额,
    SUM(CASE WHEN MONTH(ywdate) = 3 THEN decInvoiceAmoun ELSE 0 END) AS 三月金额,
    SUM(CASE WHEN MONTH(ywdate) = 4 THEN decInvoiceAmoun ELSE 0 END) AS 四月金额,
    SUM(CASE WHEN MONTH(ywdate) = 5 THEN decInvoiceAmoun ELSE 0 END) AS 五月金额,
    SUM(CASE WHEN MONTH(ywdate) = 6 THEN decInvoiceAmoun ELSE 0 END) AS 六月金额,
    SUM(CASE WHEN MONTH(ywdate) = 7 THEN decInvoiceAmoun ELSE 0 END) AS 七月金额,
    SUM(CASE WHEN MONTH(ywdate) = 8 THEN decInvoiceAmoun ELSE 0 END) AS 八月金额,
    SUM(CASE WHEN MONTH(ywdate) = 9 THEN decInvoiceAmoun ELSE 0 END) AS 九月金额,
    SUM(CASE WHEN MONTH(ywdate) = 10 THEN decInvoiceAmoun ELSE 0 END) AS 十月金额,
    SUM(CASE WHEN MONTH(ywdate) = 11 THEN decInvoiceAmoun ELSE 0 END) AS 十一月金额,
    SUM(CASE WHEN MONTH(ywdate) = 12 THEN decInvoiceAmoun ELSE 0 END) AS 十二月金额
FROM 
    view_documentall
where """+where+"""
GROUP BY 
    orgcode,nReimburserCode, nReimburserItem
ORDER BY 
    orgcode,nReimburserCode, nReimburserItem;

"""
            tableList=conn.rows(sql)
            rowsMap=[:]
            rowsfun()
        }catch(Exception  e){
            e.printStackTrace()
        }finally{
            if(conn!=null){
                conn.close()
            }
        }
    }
    def getdatamoney(def MONTH){
        def sum=0.00
        try{
            tableList.iterator().each{
                sum+=it.get(MONTH)
            }
        }catch(Exception  e){
            e.printStackTrace()
        }
        return sum

    }





}
